import configparser
import logging

import pymysql

cf = configparser.ConfigParser()
cf.read("mysql/config.ini")

HOST = cf.get("mysql", "host")
PORT = int(cf.get("mysql", "port"))
USER = cf.get("mysql", "user")
PASSWORD = cf.get("mysql", "password")
DB = cf.get("mysql", "db")


class MysqlConnect:
    conn = None
    # 可以执行sql语句的光标对象
    cursor = None

    def __init__(self, host=HOST, port=PORT, user=USER, password=PASSWORD, db=DB):
        # autocommit=True表示执行sql后提交到真正的数据库，否则只是伪执行，需要显示使用conn.commit提交执行
        self.conn = pymysql.Connect(host=host, port=port, user=user, password=password, db=db, charset='utf8',
                                    autocommit=True)
        # self.cursor = self.conn.cursor()
        # cursor默认返回数据为元组，这里配置成返回字典
        self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

    def __close__(self):
        self.cursor.close()
        self.conn.close()

    def sqlRun(self, sql):
        try:
            logging.info('sql-->' + sql)
            self.cursor.execute(sql)
            logging.info('affect rowcount:' + str(self.cursor.rowcount))
            return self.cursor.rowcount
        except:
            # 发生错误回滚
            logging.info('exception occur!rollback')
            self.conn.rollback()

    def insert(self, map, entity, op_type='INSERT'):
        '''
        根据实体属性列和map动态构造insert语句
        :param map:
        :param entity:
        :param op_type:
        :return:
        '''
        sql = op_type + ' INTO ' + entity.gettablename() + '({}) VALUES({})'
        fields = entity.getfields()
        fields.sort()
        fieldstr = ''
        # 按key将map排序，拿到排序后的value
        keylist = sorted(map)
        # 去掉实体中没有的属性
        validkeylist = [key for key in keylist if key in fields]
        values = []
        for key in validkeylist:
            values.append(map[key])
        valuestr = ''
        # 按列名遍历，按值可能有重复
        for f in validkeylist:
            index = fields.index(f)
            fieldstr += '`' + f + '`,'
            valuestr += '"' + str(values[index]) + '",'
        fieldstr = fieldstr[0:-1]
        valuestr = valuestr[0:-1]
        sql = sql.format(fieldstr, valuestr)
        try:
            logging.info('sql-->' + sql)
            self.cursor.execute(sql)
            logging.info('affect rowcount:' + str(self.cursor.rowcount))
            return self.cursor.rowcount
        except:
            self.conn.rollback()
            return 'error'

    def replace(self, map, entity):
        return self.insert(map, entity, 'REPLACE')

    def select(self, sql):
        try:
            logging.info('sql-->' + sql)
            self.cursor.execute(sql)
            return self.cursor.fetchall()
        except:
            logging.info('unable to fetch data!')
            return 'error'
